package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xmy.cultivate.entity.OrderApply;
import com.xmy.cultivate.entity.Staff;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.TypeValueData;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * <p>
 * 员工 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-07-26
 */
public interface StaffMapper extends BaseMapper<Staff> {

    @Select("select * from (select t1.*,t2.name as superior_name,t3.`name` as station_name,getSchoolName(t1.school_id) as school_name from staff as t1 LEFT JOIN staff as t2 on t1.superior_id = t2.id LEFT JOIN station as t3 on t1.station_id = t3.id) as t where id=#{id} and deleted =0")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(
                    property = "adminObj",
                    column = "admin_id",
                    one = @One(select = "com.xmy.cultivate.mapper.AdminMapper.selectById")
            )
    })
    public abstract Staff findOneById(@Param("id") Long id);


    @Select("select * from (select t1.*,t2.name as superior_name,t3.`name` as station_name,getSchoolName(t1.school_id) as school_name from staff as t1 LEFT JOIN staff as t2 on t1.superior_id = t2.id LEFT JOIN station as t3 on t1.station_id = t3.id) as t ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(
                    property = "adminObj",
                    column = "admin_id",
                    one = @One(select = "com.xmy.cultivate.mapper.AdminMapper.selectById")
            )
    })
    public abstract IPage<Staff> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 根据教师编号获取教师名字
     *
     * @param id
     * @return
     */
    @Select("SELECT name FROM staff WHERE id=#{id}")
    String getNameById(@Param("id") Long id);


    /**
     * 获取差集
     *
     * @param page
     * @param wrapper
     * @return
     */
    @Select("SELECT staff.id,staff.name FROM staff LEFT JOIN role_admin ON staff.id=role_admin.admin_id ${ew.customSqlSegment})")
    Page<Staff> listCollection(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select * from staff where admin_id = ${adminId}")
    Staff getOneForAdminid(@Param("adminId") Long adminId);

    /**
     * 工龄计算
     *
     * @param wrapper
     * @return
     */
    @Select("SELECT staff.`name`,staff.`status`,staff.entry_date,staff.leave_date,DATEDIFF(IFNULL( staff.leave_date, CURDATE()), staff.entry_date) AS 'working' FROM staff LEFT JOIN role_admin ON role_admin.admin_id = staff.admin_id ${ew.customSqlSegment}")
    List<Staff> working(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select({
            "<script>",
            "SELECT ",
            "staff.id,",
            "staff.`name`,",
            "course.subjects_id AS subjectsId,",
            "grade.school_id as schoolId,",
            "role.id AS roleId ",
            "FROM ",
            "staff ",
            "LEFT JOIN grade ON grade.teacher_id = staff.id ",
            "LEFT JOIN course ON grade.course_id = course.id ",
            "LEFT JOIN role_admin ON role_admin.admin_id = staff.id ",
            "LEFT JOIN role ON role.id = role_admin.role_id  ",
            "WHERE ",
            "staff.status &lt;&gt; 2 ",
            "AND staff.deleted &lt;&gt; 1  ",
            "AND staff.`name` NOT LIKE '%前台%' ",
            "AND staff.`name` NOT LIKE '%总部%' ",
            "AND staff.`name` NOT LIKE '%市场部%' ",
            "AND staff.`name` &lt;&gt; '111' ",
            "<when test='(schoolId!=null and schoolId!=&apos;&apos;) || (subjectId!=null and subjectId!=&apos;&apos;)'>",
            "AND grade.id IN ( ",
            "SELECT grade_id ",
            "FROM ",
            "student_grade ",
            "WHERE ",
            "`quarter_num` = ${quarterNum} ",
            "AND year_part = ${yearPart} ",
            "AND staff.`name` NOT LIKE '%前台%' ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND school_id = ${schoolId} ",
            "</when>",
            "<when test='subjectId!=null and subjectId!=&apos;&apos;'>",
            "AND subjects_id = ${subjectId} ",
            "</when>",
            "AND STATUS &lt;&gt; 3 ",
            "AND is_check_house = 0 ",
            "AND deleted = 0 ",
            "AND lesson_type = 1 ",
            "GROUP BY grade_id ) ",
            "AND grade.deleted &lt;&gt; 1  ",
            "AND course.deleted &lt;&gt; 1  ",
            "</when>",
            "<when test='roleId!=null and roleId!=&apos;&apos;'>",
            "AND role_id = ${roleId} ",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos;'>",
            "AND staff.`name` LIKE '%${name}%' ",
            "</when>",
            "<when test='subjectId!=null and subjectId!=&apos;&apos;'>",
            "AND course.subjects_id = ${subjectId} ",
            "</when>",
            "GROUP BY ",
            "staff.id ",
            "ORDER BY ",
            "staff.id ",
            "</script>"
    })
    List<Staff> staffAnalyse(@Param("quarterNum") String quarterNum,@Param("yearPart") String yearPart,@Param("roleId") String roleId,@Param("name") String name,@Param("schoolId") String schoolId,@Param("subjectId") String subjectId);


    @Select("SELECT COUNT(id) FROM staff WHERE phone = #{phone} and deleted = 0")
    public abstract Integer staffCountByPhone(@Param("phone") String phone);
    
    @Select({"<script>",
            "SELECT",
                    "t1.teacher_id ",
                    "FROM",
                    "grade AS t1",
                    "INNER JOIN course AS t2 ON t1.course_id = t2.id ",
                    "WHERE",
                    "t1.lesson_type = 1 ",
                    "AND t2.subjects_id in(${subjectsIds})",
                    "GROUP BY",
                    "t1.teacher_id",
    "</script>"})
    public List<Long> getTeacherIdForSubjects(@Param("subjectsIds") String subjectsIds);


}
